<?php
 /**
  * oa-hr导入用户工作信息
  * **/
apf_require_class('Oa_Job_Attendance_Abstract');
class Oa_Job_Attendance_OaHrSynergyWorkInfo extends Oa_Job_Attendance_Abstract {

    /** 数据库读 **/
    private $readdb = 'hroa_slave';

    /** 数据库写 **/
    private $writedb = 'hroa_master';

    /** oa表 **/
    private $oa_readdb = 'attendance_slave';

    /** 金蝶数据库读 **/
    private $kdreaddb = 'kd_slave';

    /**
     * 用户信息,id code superior_code
     */
    private $user_ids;

    public function run() {
        ini_set("memory_limit",'512M');
        $start = microtime(true);
        $this->log("开始执行啦！");
        $this->db_connect();
       //同步人员信息
        $this->syn_userworkinfo();

        $this->log ( "开始..." );
        //导出金蝶里面坏的部门数据
        //$this->import_bad_department_info();
        $end = microtime(true) - $start;
        $this->log("共耗时:".$end);
        $this->log("job执行结束！");
    }

    private function _get_user_info() {
        $s_sql = "select kd_em_id,user_id from user_info";
        $oa_userinfo_data = $this->get_db_rows_assoc($s_sql, $this->oa_readdb);
        return $oa_userinfo_data;
    }

    public function syn_userworkinfo() {
        #1.获得用户em_id与user_id的对应关系 [em_id]=>array('user_id')
        $user_info = $this->_get_user_info();
        $s_sql = "select emph.em_id,emph.employeename,emph.positionid,op.name,emph.begindate
                  from HM_EM_PositionHistory as emph
                  left join org_position as op on emph.positionid = op.id
                  order by begindate desc";
        #2.获得em_id与position的对应关系
        if(empty($user_info)) {return false;}

        $result = mssql_query($s_sql);

        $data = array();
        $is_have_data = array();
        while( $row = mssql_fetch_assoc($result) ) {
            $em_id = trim(mssql_guid_string($row['em_id']));
            if(in_array($em_id,$is_have_data)) {continue;}
            $is_have_data[] = $em_id;

            if(isset($user_info[$em_id])) {
                $user_info[$em_id]['begindate'] = $this->_get_work_time($row['begindate']);
                $this->_update(array("work_time"=>$user_info[$em_id]['begindate']),"work",array("user_id"=>$user_info[$em_id]["user_id"]));
            }
        }
    }

    private function _get_work_time($time) {
        $time = trim($time);
        if(empty($time)) {
            return '';
        }

        return date("Ymd",strtotime($time));
    }

    //同步人员信息
    public function syn_userinfo_data() {
        $orgin_oa_userinfo_data = $this->get_origin_user_info();
        $s_sql = "select
                  empl.EM_ID,empl.Name,empl.Code,empla.HRMS_UserField_1,empl.EMail,empl.Sex,empl.Telphone,empl.Mobile,empla.FEnglishName,
                  hminfo.EnterGroupDate,hminfo.InDueDate,empl.DimissionTime,empl.Status as onornot,hminfo.ProbationTerm,
                  empo.PositionName,empo.JobName,empo.UnitID,empo.UnitName,empo.BusinessGroupName,empo.CompanyName,empo.ServicePeriod,empo.EmployeeType,
                  emdi.Status,emdi.EnterDate,emdi.InDueDate,emdi.JobClassName,emdi.JobGrade,emdi.CompanyCode,emdi.DimissionType,emdi.FireType,
                  empl.IDCardID,empl.PastportNum,empl.Birthday,empl.NativePlace,empl.IDAddress,empl.NowWorkSpeciality,empl.MarriedStatus,empl.BankName,empl.BankAccountNum,
                  empla.NowResidence,
                  dinfo.Reason,
                  emcon.BeginDate,emcon.EndDate
                  from HM_Employees as empl
                  left join HM_EmployeesAddInfo as empla on empl.EM_ID=empla.EM_ID
                  left join HM_Em_WorkInfo as hminfo on empl.EM_ID = hminfo.EM_ID
                  left join v_Report_Workinfo as empo on empl.EM_ID = empo.EM_ID
                  left join v_Report_EmployeeDetailInfo as emdi on empl.EM_ID = emdi.EM_ID
                  left join V_Report_DimissionUnionFireInfo as dinfo on empl.EM_ID = dinfo.EM_ID
                  left join HM_EM_Contracts as emcon on empl.EM_ID = emcon.EM_ID";
        $result = mssql_query($s_sql);
        $data = array();
        $time = time();
        $is_have_list = array();//一个人有多个重复的离职信息，所以需要过滤
        while( $row = mssql_fetch_assoc($result) ) {
            if(in_array($row['Code'],$is_have_list)) {continue;}
            //if($this->set_utf8($row['Status']) == "离职") {continue;}
            $is_have_list[] = $row['Code'];
            $rs = array();
            //$rs['kd_em_id'] = mssql_guid_string($row['EM_ID']);
            $rs['user_id'] = $orgin_oa_userinfo_data[$row['Code']]['user_id'];
            if(empty($rs['user_id']) || empty($row['Code'])) {continue;}
            $rs['user_name'] = $this->set_utf8($row['Name']);
            $rs['real_name'] = $this->set_utf8($row['Name']);
            $rs['sex']       = $row['Sex'];
            $rs['identity_code'] = $this->_get_idcard_id($row['IDCardID'],$row['PastportNum']);//身份证或护照
            $rs['identity_type'] = $this->_get_idcard_type($row['IDCardID'],$row['PastportNum']);//身份证或护照类型 1:身份证；2：护照
            $rs['birthday'] = $this->_get_date($row['Birthday']);//获得生日  19601011
            $rs['native_place'] = $this->set_utf8($row['NativePlace']);//籍贯
            $rs['identity_address'] = $this->set_utf8($row['IDAddress']);//身份证地址
            $rs['present_address'] = $this->set_utf8($row['NowResidence']);//现居地址
            $rs['degree'] = $this->_get_degree_id($this->set_utf8($row['NowWorkSpeciality'])); //学位
            $rs['graduate_time'] = ""; //毕业时间
            $rs['graduate_university'] = ""; //毕业院校
            $rs['profession'] = ""; //专业
            $rs['marital_status'] = $this->set_utf8($row['MarriedStatus']); //婚姻状态 1:未婚 2：已婚
            $rs['fertility_status'] = "";// 1:未生育 2：已生育
            $rs['english_name'] = $row['FEnglishName'];
            $rs['office_phone'] = $this->_get_phone($row['HRMS_UserField_1']);//公司电话号码
            $rs['office_phone_ex'] = $this->_get_phone_ex($row['HRMS_UserField_1']);//电话及分机号
            $rs['cellphone'] = empty($row['Mobile']) ? "0":$row['Mobile'];//手机
            $rs['office_mail'] = empty($row['EMail']) ? "0":$row['EMail'];//邮箱
            $rs['emergency_people'] = "";//紧急联系人
            $rs['emergency_phone'] = "";//紧急联系电话
            $rs['company_id'] = $this->_get_company_id($row['CompanyCode']);//签约公司

            $rs['city_id']    = $this->_get_city_id($this->set_utf8($row['CompanyName']));//签约城市ID
            $rs['work_city_id'] = $this->_get_city_id($this->set_utf8($row['CompanyName']));//工作城市ID
            $rs['user_code']    = $row['Code'];
            $rs['user_type']    = $this->_get_user_type($this->set_utf8($row['EmployeeType'])); //1:试用期员工,2:正式员工，3:实习生，4:兼职,5:临时工
            $rs['job_status']   = $this->_get_job_status($orgin_oa_userinfo_data[$row['Code']]['job_status'],$orgin_oa_userinfo_data[$row['Code']]['dimission_date']);//1:在职，2：离职
            $rs['leaving_type'] = $this->_get_leaving_type($this->set_utf8($row['DimissionType']),$this->set_utf8($row['FireType']));
            $rs['reason_id'] = "";//金蝶的离职原因与现在的不符，故留空
            $rs['other_reason'] = "";//其他离职原因
            $rs['enter_date'] = $this->_get_date($row['EnterGroupDate']); //入职时间
            $rs['indue_date'] = $this->_get_date($row['InDueDate']);//转正时间
            $rs['leaving_date'] = $this->get_dimissionTime($row['onornot'],$row['DimissionTime']);//离职时间
            $rs['last_date'] = $this->get_dimissionTime($row['onornot'],$row['DimissionTime']);//最后工作日
            $rs['join_start'] = $this->_get_date($row['BeginDate']);//合同开始时间
            $rs['join_end'] = $this->_get_date($row['EndDate']);//合同结束时间
            $rs['join_keep'] = $this->_get_join_keep($row['BeginDate'],$row['EndDate']);//合同年限

            $rs['trial_start'] = $this->_get_date($row['EnterGroupDate']);//试用期开始时间
            $rs['trial_end']   = $this->_get_date($row['InDueDate']);//试用期结束时间
            $rs['trial_keep']  = $row['ProbationTerm'];//试用期

            $rs['bank_name']   = $this->set_utf8($row['BankName']);
            $rs['bank_card']   = $row['BankAccountNum'];
            $rs['p_level']   = $this->_get_p_level($row['JobClassName'],$row['JobGrade'],"P"); //获得p级
            $rs['m_level']   = $this->_get_p_level($row['JobClassName'],$row['JobGrade'],"M"); //获得m级
            $rs['new_train']   = "1";  //新员工是否培训
            $rs['created']   = $time; //创建时间

//             if(isset($oa_userinfo_data[$rs['user_code']])) {
//                 $this->log("同步");
//                 unset($rs['created']);
//                 unset($rs['user_code']);
//                 var_dump($this->_update_data($rs,array('user_code'=>$row['Code'])));
//             }else {
                $this->log("新增");
                var_dump($this->_insert_data($rs));
            //}
        }
        return true;
    }


    private function _update ($data = array(),$tablename = "",$where = array(),$status = FALSE) {
        if(empty($tablename)) {
            return false;
        }
        $data_count  = count($data);
        if ($data_count < 1) {
            return false;
        }
        if (empty($where)) {
            return false;
        }
        //防止误更新
        if (is_numeric($where)) {
            return false;
        }
        $sql = "UPDATE " . $tablename . " SET ";
        $i = 1;
        foreach ($data as $key => $value) {
            if(empty($value) && $value!==0) {continue;}
            $v = self::escape_value($value);
            $sql .= $status ? "`{$key}`= $v ":"`{$key}` = '{$v}'";
            $sql .= ($i < $data_count)?',':'';
            $i ++;
        }

        $s = substr($sql,strlen($sql)-1,1);
        if($s == ",") {
            $sql = substr($sql,0,strlen($sql)-1);
        }
        $_where = $this->build_where($where);
        $sql .= @$_where['where'];
        return $this->execute($sql, $_where['params'], $this->writedb);
    }

    private function build_where ($where) {
        $_where = array('where' => '', 'params' => array());
        if (empty($where)) {
            return $_where;
        }
        if (is_array($where) && count($where)) {
            foreach ($where as $key => $value) {
                if (preg_match("/\?/", $key)) {
                    $_where['field'][] = '(' . $key . ')';
                } else {
                    $_where['field'][] = '(`' . $key . '` = ?)';
                }
                $_where['params'][] = self::escape_value($value);
            }
            $_where['where']=' WHERE ' . implode(' AND ',$_where['field']);
        }else{
            $_where['where']=' WHERE ' . $where;
        }
        return $_where;
    }

    public static function escape_value ($value) {
        if (is_numeric($value) || is_string($value)) {
            return str_replace(array("'",'\\'),array("''",'\\\\'),$value);
        } else {
            return $value;
        }
    }

    private function _insert($arr,$tablename) {
        if(empty($tablename)) {return false;}
        $param = array();
        $values = array();
        foreach($arr as $k => $v) {
            if(empty($v) && $v!==0) {
                unset($arr[$k]);
                continue;
            }
            $values[] = "?";
            $param[] = $v;

        }
        $sql = "INSERT INTO " . $tablename . " (`";
        $sql .= implode('`,`', array_keys($arr));
        $sql .= "`) VALUES (";
        $sql .= implode(",", $values) . ")";


        //$sql = "INSERT INTO user (`user_name`,`real_name`,`sex`,`identity_code`,`identity_type`,`birthday`,`native_place`,`identity_address`,`present_address`,`degree`,`graduate_time`,`graduate_university`,`profession`,`marital_status`,`fertility_status`,`english_name`,`office_phone`,`office_phone_ex`,`cellphone`,`office_mail`,`emergency_people`,`emergency_phone`,`company_id`,`city_id`,`work_city_id`,`user_code`,`user_type`,`job_status`,`leaving_type`,`reason_id`,`other_reason`,`enter_date`,`indue_date`,`leaving_date`,`last_date`,`join_start`,`join_end`,`join_keep`,`trial_start`,`trial_end`,`trial_keep`,`bank_name`,`bank_card`,`p_level`,`m_level`,`new_train`,`created`) VALUES ('0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0')";

        $rs = $this->execute($sql, $param, $this->writedb);
        return $rs;
    }

    public function set_utf8($str) {
        $origin = mb_detect_encoding($str,array('CP936','UTF-8','ASCII','GBK','GB2312'));
        return mb_convert_encoding($str,'UTF-8',$origin);
    }

    /**
     * 清空数据库
     * @param unknown_type $db_name
     */
    private function clear_db($db_name){
        $sql = "delete from $db_name";
        echo $sql;
        return $this->execute($sql,array(),$this->writedb);
    }

    /**
     * 重置自增id
     * @param unknown_type $db_name
     */
    private function alter_db($db_name){
        $sql = "alter table $db_name AUTO_INCREMENT=1";
        return $this->execute($sql,array(),$this->writedb);
    }

    public function db_connect() {
        $dbhost = "192.168.1.60";
        $dbusname = "sa";
        $dbpasswd = "sa";
        $dbname = "AIS20100415101750";
        $conn = mssql_connect(
            $dbhost,
            $dbusname,
            $dbpasswd
        );
        mssql_select_db($dbname,$conn);
    }
}
